package exam;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * UserDao接口的MySQL数据库实现
 * 使用TB_USER表进行操作
 */
public class UserDaoImpl implements UserDao {
    // 数据库连接配置
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    // 静态代码块加载数据库驱动
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            throw new RuntimeException("加载MySQL驱动失败", e);
        }
    }

    /**
     * 获取数据库连接
     * @return Connection对象
     * @throws SQLException 如果获取连接失败
     */
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    @Override
    public void createTable() {
        String sql = "CREATE TABLE IF NOT EXISTS TB_USER (" +
                "userno VARCHAR(20) PRIMARY KEY, " +
                "userName VARCHAR(50) NOT NULL, " +
                "password VARCHAR(100) NOT NULL, " +
                "job VARCHAR(50), " +
                "sal DECIMAL(10,2)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
            System.out.println("用户表TB_USER创建成功");
        } catch (SQLException e) {
            throw new RuntimeException("创建用户表失败", e);
        }
    }

    @Override
    public void insert(User user) {
        String sql = "INSERT INTO TB_USER (userno, userName, password, job, sal) VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, user.getUserno());
            pstmt.setString(2, user.getUserName());
            pstmt.setString(3, user.getPassword());
            pstmt.setString(4, user.getJob());
            pstmt.setDouble(5, user.getSal());

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("创建用户失败，没有行受影响");
            }

            System.out.println("用户添加成功: " + user);
        } catch (SQLException e) {
            throw new RuntimeException("添加用户失败", e);
        }
    }

    @Override
    public boolean login(String userno, String password) {
        String sql = "SELECT password FROM TB_USER WHERE userno = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, userno);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    String dbPassword = rs.getString("password");
                    return dbPassword.equals(password);
                }
                return false;
            }
        } catch (SQLException e) {
            throw new RuntimeException("登录验证失败", e);
        }
    }

    @Override
    public void update(User user) {
        String sql = "UPDATE TB_USER SET userName = ?, password = ?, job = ?, sal = ? WHERE userno = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, user.getUserName());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getJob());
            pstmt.setDouble(4, user.getSal());
            pstmt.setString(5, user.getUserno());

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("更新用户失败，没有行受影响");
            }

            System.out.println("用户更新成功: " + user);
        } catch (SQLException e) {
            throw new RuntimeException("更新用户失败", e);
        }
    }

    @Override
    public void delete(String[] usernos) {
        if (usernos == null || usernos.length == 0) {
            return;
        }

        // 构建IN子句参数占位符
        StringBuilder placeholders = new StringBuilder();
        for (int i = 0; i < usernos.length; i++) {
            placeholders.append("?");
            if (i < usernos.length - 1) {
                placeholders.append(",");
            }
        }

        String sql = "DELETE FROM TB_USER WHERE userno IN (" + placeholders + ")";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置参数
            for (int i = 0; i < usernos.length; i++) {
                pstmt.setString(i + 1, usernos[i]);
            }

            int affectedRows = pstmt.executeUpdate();
            System.out.println("成功删除 " + affectedRows + " 条用户记录");
        } catch (SQLException e) {
            throw new RuntimeException("删除用户失败", e);
        }
    }

    @Override
    public List<User> getAll() {
        List<User> userList = new ArrayList<>();
        String sql = "SELECT * FROM TB_USER ORDER BY userno";

        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                User user = resultSetToUser(rs);
                userList.add(user);
            }
        } catch (SQLException e) {
            throw new RuntimeException("获取用户列表失败", e);
        }
        return userList;
    }

    @Override
    public List<User> getUserForPage(int pageSize, int pageNum) {
        List<User> userList = new ArrayList<>();
        int offset = (pageNum - 1) * pageSize;
        String sql = "SELECT * FROM TB_USER ORDER BY userno LIMIT ?, ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, offset);
            pstmt.setInt(2, pageSize);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    User user = resultSetToUser(rs);
                    userList.add(user);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("分页查询用户失败", e);
        }
        return userList;
    }

    @Override
    public User getUserByNo(String userno) {
        String sql = "SELECT * FROM TB_USER WHERE userno = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, userno);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return resultSetToUser(rs);
                }
                return null;
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据用户编号查询用户失败", e);
        }
    }

    /**
     * 将ResultSet转换为User对象
     * @param rs ResultSet对象
     * @return User对象
     * @throws SQLException 如果数据库访问出错
     */
    private User resultSetToUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setUserno(rs.getString("userno"));
        user.setUserName(rs.getString("userName"));
        user.setPassword(rs.getString("password"));
        user.setJob(rs.getString("job"));
        user.setSal(rs.getDouble("sal"));
        return user;
    }
}